<?php

namespace app\api\controller;

use app\common\controller\Api;
use app\common\library\Sms as Smslib;
use PHPExcel;
use PhpOffice\PhpSpreadsheet\Cell\DataValidation;

use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\NamedRange;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use think\Db;
use think\Log;

/**
 * 首页接口
 */
class Index extends Api
{
    protected $noNeedLogin = ['*'];
    protected $noNeedRight = ['*'];

    /**
     * 首页
     *
     */
    public function index()
    {
        $config = get_addon_config('third');

        $appId = $config['wechat']['app_id'];
        $appSecret = $config['wechat']['app_secret'];
        $url = "https://api.weixin.qq.com/sns/jscode2session";
        $arr['appid'] = $appId;
        $arr['secret'] = $appSecret;
        $arr['js_code'] = "0d1OkV0w3xMpX23tKv3w3c99Lu0OkV0r";
        $arr['grant_type'] = 'authorization_code';
        $appid_data = \app\common\model\User::sendCurl(stripslashes($url), $arr, 'get',
            array('User-Agent: Apifox/1.0.0 (https://apifox.com)'));
        echo "post_data_mi：".json_encode($arr)."__appid_data:".json_encode($appid_data);
        $appid_arr = json_decode($appid_data,true);
        var_dump($appid_arr);
        $this->success('请求成功');
    }
    public function shiyi(){
        $shiyitu = Db::name("config")->where(['name'=>'shiyitu'])->value("value");
        $url = Db::name("config")->where(['name'=>'url'])->value("value");
        $this->success('请求成功',$url.$shiyitu);
    }
    public function chengName(){
       $list = Db::name("work")->order("id desc")->limit(0,2000)->select();
       foreach ($list as $k=>$v){
            $name = Db::name("product")->where(['id'=>$v['product_id']])->value("name");
           $data_id = Db::name("productname")->where(['name'=>$name,'deletetime'=>null])->find();
           db::name("work")->where(['id'=>$v["id"]])->update(['productname_id'=>$data_id['id']]);
       }
    }
    public function productnameName(){
        $list = Db::name("product")->select();
        foreach ($list as $k=>$v){
            $data_id = Db::name("productname")->where(['name'=>$v['name'],'deletetime'=>null])->find();
            if($data_id){
                db::name("product")->where(['id'=>$v["id"]])->update(['productname_id'=>$data_id['id']]);
            }

        }
    }
    public function tongzhi(){
        $list = Db::name("work")->where(['status'=>5])->select();
        foreach ($list as $k=>$v){
            $t = time()-$v['confirm_time'];
           // echo $t."_";
            if($t > 259200){
                /*echo $v['id']."#######";
                $phone = Db::name("factory")->where(['id'=>$v['factory_id']])->value("phone");
                $ar['code'] = 5173;
                //$result = Smslib::send($phone, null, 'mobilelogin');
                $params = [
                    'order' => $v['id']
                ];
                $result = Smslib::notice($phone, $params, "SMS_479760122");*/
               Db::name("work")->where(['id'=>$v['id']])->update(['is_gq'=>1]);
               // exit;
            }
        }
        $list = Db::name("work")->where(['is_gq'=>1,'status'=>['<>',5]])->select();
        foreach ($list as $k=>$v){

                Db::name("work")->where(['id' => $v['id']])->update(['is_gq' => 0]);

        }
    }
    public function demo()

    {

        $subject = 'demo';

        $title = ['工种','考试科目','申报条件','姓名','性别','手机号','证件号码','出生年月','考生类别','学历','专业','现工作地址','职业资格/技能等级证书(如：无，则后面5项无需填写)','职称证书名称','职业资格等级','证书编号','发证日期','发证单位','有无专业技术职称证书(如：无，则后面5项无需填写)','职称证书名称','专业技术等级','证书编号','发证日期','发证单位','领取方式','邮寄地址','备注'];



        /***** 下拉列表联动格式 *****/



        $data = [

            [

                'name' => '湖北省', // 省

                'children' => [

                    [

                        'name' => '武汉市', // 市

                        'children' => ['江夏区','洪山区','青山区','武昌区','汉口'] // 区，县

                    ],

                    [

                        'name' => '宜昌市',

                        'children' => ['当阳市','夷陵区','庙前']

                    ],

                    [

                        'name' => '荆州市',

                        'children' => ['荆州区','荆州城区']

                    ]

                ]

            ],



        ];



        $high = 0;

        $objPHPExcel = new \PHPExcel();

        $titleRow = array('A1','B1','C1','D1','E1','F1','G1','H1','I1','J1','K1','L1','M1','N1','O1','P1','Q1','R1','S1','T1','U1','V1','W1','X1','Y1','Z1','AA1','AB1','AC1','AD1');

        for($a = 0; $a < count($title); $a++){

            // 设置单元格的值

            $objPHPExcel->setActiveSheetIndex(0)->setCellValue($titleRow[$a], $title[$a]);

        }

        $supportSheet = new \PHPExcel_Worksheet($objPHPExcel, 'support'); //创建一个工作表

        $objPHPExcel->addSheet($supportSheet); //插入工作表

        $col = ['A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z','AA'];



        /*********** 下拉列表联动 ***********/

        foreach ($data as $key=>$first){

            $objPHPExcel->getSheetByName('support')->setCellValue($col[0].($key+1+$high),$first['name']);

            $max = 0; //重置max

            $secondNum = count($first['children']);

            foreach ($first['children'] as $index=>$second){

                $objPHPExcel->getSheetByName('support')->setCellValue($col[$index+1].($key+1+$high),$second['name']);

                $thirdNum = count($second['children']);

                if ($thirdNum > $max){

                    $max = $thirdNum;

                }

                foreach ($second['children'] as $id=>$third){

                    $objPHPExcel->getSheetByName('support')->setCellValue($col[$index+1].($key+1+$high+$id+1),$third);

                }

                //定义三级名称

                $objPHPExcel->addNamedRange(

                    new \PHPExcel_NamedRange(

                        $second['name'],

                        $objPHPExcel->getSheetByName('support'),

                        $col[$index+1].($key+1+$high+1).':'.$col[$index+1].($key+1+$high+1+$thirdNum-1)

                    )

                );

            }

            //定义二级名称

            $objPHPExcel->addNamedRange(

                new \PHPExcel_NamedRange(

                    $first['name'],

                    $objPHPExcel->getSheetByName('support'),

                    $col[1].($key+1+$high).':'.$col[1+$secondNum-1].($key+1+$high)

                )

            );

            $high += $max;

        }

        //移花接木

        foreach ($data as $var=>$content){

            $objPHPExcel->getSheetByName('support')->setCellValue('UI'.($var+1),$content['name']);

        }





        //定义顶级名称

        /*$total = count($data);

        $str = '';

        $count = 0;

        $max = 0;

        for ($i = 0;$i < $total;$i++){

            $str .= $col[0].(1+$count+$i).',';

            $secondCount = count($data[$i]['children']);

            for ($j = 0;$j < $secondCount;$j++){

                if (count($data[$i]['children'][$j]['children']) > $max){

                    $max = count($data[$i]['children'][$j]['children']);

                }

            }

            $count += $max;

        }

        $str = rtrim($str,',');

        $objPHPExcel->addNamedRange(

            new \PHPExcel_NamedRange(

                'region',

                $objPHPExcel->getSheetByName('support'),

                $str

            )

        );*/

        $total = count($data);

        $objPHPExcel->addNamedRange(

            new \PHPExcel_NamedRange(

                'region',

                $objPHPExcel->getSheetByName('support'),

                'UI1'.':'.'UI'.$total

            )

        );



        //数据验证

        for ($i = 2;$i <= 500;$i++){

            $objValidation = $objPHPExcel->getActiveSheet()->getCell('A'.$i)->getDataValidation(); // 设置在A列

            $objValidation->setType(\PHPExcel_Cell_DataValidation::TYPE_LIST );

            $objValidation->setErrorStyle(\PHPExcel_Cell_DataValidation::STYLE_INFORMATION );

            $objValidation->setAllowBlank(false);

            $objValidation->setShowInputMessage(true);

            $objValidation->setShowErrorMessage(true);

            $objValidation->setShowDropDown(true);

            $objValidation->setErrorTitle('输入错误');

            $objValidation->setError('不在列表中的值');

            $objValidation->setPromptTitle('请选择');

            $objValidation->setPrompt('请从列表中选择一个值.');

            $objValidation->setFormula1("=region"); //设置公式1



            $objValidation = $objPHPExcel->getActiveSheet()->getCell('B'.$i)->getDataValidation(); // 设置在B列

            $objValidation->setType(\PHPExcel_Cell_DataValidation::TYPE_LIST );

            $objValidation->setErrorStyle(\PHPExcel_Cell_DataValidation::STYLE_INFORMATION );

            $objValidation->setAllowBlank(false);

            $objValidation->setShowInputMessage(true);

            $objValidation->setShowErrorMessage(true);

            $objValidation->setShowDropDown(true);

            $objValidation->setErrorTitle('输入错误');

            $objValidation->setError('不在列表中的值');

            $objValidation->setPromptTitle('请选择');

            $objValidation->setPrompt('请从列表中选择一个值.');

            $objValidation->setFormula1('=INDIRECT($'.'A'.'$'.$i.')'); //设置公式1，绑定A列



            $objValidation = $objPHPExcel->getActiveSheet()->getCell('C'.$i)->getDataValidation(); // 设置在C列

            $objValidation->setType(\PHPExcel_Cell_DataValidation::TYPE_LIST );

            $objValidation->setErrorStyle(\PHPExcel_Cell_DataValidation::STYLE_INFORMATION );

            $objValidation->setAllowBlank(false);

            $objValidation->setShowInputMessage(true);

            $objValidation->setShowErrorMessage(true);

            $objValidation->setShowDropDown(true);

            $objValidation->setErrorTitle('输入错误');

            $objValidation->setError('不在列表中的值');

            $objValidation->setPromptTitle('请选择');

            $objValidation->setPrompt('请从列表中选择一个值.');

            $objValidation->setFormula1('=INDIRECT($'.'B'.'$'.$i.')'); //设置公式1，绑定B列

        }

        /*********** 下拉列表联动结束 ***********/



        /************ 下拉列表 ************/



        // 级别

        // $enroll_level = Dictionary::query()

        //     ->where('code_type','enroll_level')

        //     ->orderBy('sort','DESC')

        //     ->pluck('name')->toArray();

        $spectials = [

            ['column'=>'E','select_options'=>['男','女']],// 性别

            // ['column'=>'F','select_options'=>$enroll_level],// 级别

        ];

        $objActSheet = $objPHPExcel->getActiveSheet();

        foreach($spectials as $spectial)

        {

            $optionsString = null;

            $optionsString = implode(',', $spectial['select_options']);



            $n = 2;

            $num = 500;

            // 我这里设置500行，可自行设置

            while($n <= $num) {

                $objValidation = $objActSheet->getCell($spectial['column'].(string)$n)->getDataValidation(); //这一句为要设置数据有效性的单元格

                // $objValidation的各项设置参数可详见phpexcel文件，

                // 目录大概为/.../phpoffice/phpexcel/Classes/PHPExcel/Writer/Excel5/Worksheet.php

                // 2767行 方法名：writeDataValidity

                $objValidation -> setType(\PHPExcel_Cell_DataValidation::TYPE_LIST)

                    -> setErrorStyle(\PHPExcel_Cell_DataValidation::STYLE_STOP)

                    -> setAllowBlank(true)

                    -> setShowInputMessage(true)

                    -> setShowErrorMessage(true)

                    -> setShowDropDown(true)

                    -> setErrorTitle('输入的值有误')

                    -> setError('您输入的值不在下拉框列表内.')

                    -> setPromptTitle('')

                    -> setPrompt('')

                    -> setOperator(\PHPExcel_Cell_DataValidation::OPERATOR_BETWEEN)

                    -> setFormula1('"'.$optionsString.'"');

                $n++;

            }

        }

        /*********** 下拉列表结束 ************/

        /************* 时间格式限制 **************/

       /* for ($ii=2; $ii<=500; $ii++)

        {

            $objPHPExcel->getActiveSheet()->getStyle("H".(string)$ii)->getNumberFormat()->setFormatCode("YYYY-m-d");

            $objPHPExcel->getActiveSheet()->getStyle("Q".(string)$ii)->getNumberFormat()->setFormatCode("YYYY/m/d");

        }*/



        //$objPHPExcel->setActiveSheetIndex(0);

        //输出表格

        $filename = $subject.date('Ymd').time().'.xlsx'; // 表格名称

        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');

        header('Content-Disposition: attachment;filename='.$filename);

        header('Cache-Control: max-age=0');



        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');

        $objWriter->save($filename); // 保存



        // 直接下载

        // ob_end_clean();//清除缓冲区,避免乱码

        // header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet; charset=utf-8'); //

        // header("Pragma: public");

        // header("Expires: 0");

        // header("Access-Control-Allow-Origin:*");

        // header("Cache-Control:must-revalidate, post-check=0, pre-check=0");

        // header("Content-Type:application/force-download");

        // header("Content-Type:application/octet-stream");

        // header("Content-Type:application/download");

        // header('Content-Disposition: attachment;filename='.$filename);

        // header('Cache-Control: max-age=0');

        // $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');

        // $objWriter->save('php://output');

        // exit();



    }
    public function export(){
        // 创建新的Spreadsheet对象
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();

// 设置标题行
        $sheet->setCellValue('A1', '选择项目');

// 添加数据验证，创建下拉列表
        $dropdownValues = [
            '项目1', '项目2', '项目3', '项目4'
        ];
        //$validationHelper = $sheet->getCell('A2')->getDataValidationHelper();
        $validationHelper = $sheet->getCell('A2')->getDataValidation();
        $validation = $validationHelper->createMultiSelectListDataValidation(); // 创建多选列表验证
        $validation->setShowDropDown(true); // 显示下拉列表
        $validation->setPromptTitle('选择项目'); // 设置提示标题
        $validation->setPrompt('请从列表中选择一个或多个项目'); // 设置提示信息
        $validation->setFormula1('"' . implode(',', $dropdownValues) . '"'); // 设置下拉选项
        $sheet->setCellValue('A2', '选择'); // 设置单元格的值，这里只是一个占位符
        $sheet->getCell('A2')->setDataValidation($validation); // 应用数据验证到单元格

// 保存文件
        $writer = new Xlsx($spreadsheet);
        $writer->save('example.xlsx');
    }
        /**
         * 下载excel模板
         * @throws \PhpOffice\PhpSpreadsheet\Exception
         * @throws \PhpOffice\PhpSpreadsheet\Writer\Exception
         */
        public function exportExcel()
        {
            //定义一个excel的header表头
            $header = ['A1' => '序号', 'B1' => '姓名', 'C1' => '证件号', 'D1' => '张数', 'E1' => '证件类型', 'F1' => '省', 'G1' => '市', 'H1' => '区/镇', 'I1' => '类别', 'J1' => '设计人', 'K1' => '完成时间', 'L1' => '备注'];
            //需要填充的联动数据
            $major = [
                [
                    'id' => 1,
                    'title' => '河南省',
                    'children' =>
                        [
                            [
                                'id' => 2,
                                'title' => '郑州市',
                                'children' =>
                                    [
                                        [
                                            'id' => 3,
                                            'title' => '郑东新区'
                                        ],
                                        [
                                            'id' => 4,
                                            'title' => '金水区'
                                        ]
                                    ]
                            ],
                            [
                                'id' => 5,
                                'title' => '信阳市',
                                'children' =>
                                    [
                                        [
                                            'id' => 6,
                                            'title' => '浉河区'
                                        ],
                                        [
                                            'id' => 7,
                                            'title' => '平桥区'
                                        ]
                                    ]
                            ]
                        ]
                ],
                [
                    'id' => 8,
                    'title' => '江苏省',
                    'children' =>
                        [
                            [
                                'id' => 9,
                                'title' => '南京市',
                                'children' =>
                                    [
                                        [
                                            'id' => 10,
                                            'title' => '秦淮区'
                                        ],
                                        [
                                            'id' => 11,
                                            'title' => '玄武区'
                                        ]
                                    ]
                            ],
                            [
                                'id' => 12,
                                'title' => '苏州市',
                                'children' =>
                                    [
                                        [
                                            'id' => 13,
                                            'title' => '姑苏区'
                                        ],
                                        [
                                            'id' => 14,
                                            'title' => '吴中区'
                                        ]
                                    ]
                            ]
                        ]
                ]
            ];
            //类别
            $typeStr = '是,否';
            //证件类型
            $biaoduanStr = '身份证,驾照,护照,学生证';
            //excel中A-Z单元格
            $letters = range('A', 'Z');

            //实例化Spreadsheet对象
            $spreadsheet = new Spreadsheet();

            $sheetMain = $spreadsheet -> getsheet(0);//主sheet
            $sheetMain -> setTitle('人员信息');//设置sheet的名称
            $sheetMain -> getPageSetup() -> setHorizontalCentered(true);
            $sheetMain -> getPageSetup() -> setVerticalCentered(false);

            foreach ($header as $key => $value) {
                $sheetMain -> setCellValue($key, $value);
            }
            $sheetMajor = $spreadsheet -> createSheet(1); //省市sheet
            $sheetProp = $spreadsheet -> createSheet(2); //市区sheet

            $sheetPropCol = 0;//属性sheet的列
            foreach ($major as $key => $value) {//省,一个省一列，第一行为市，第二行为区/镇
                $row = 1; // 专业和子专业sheet的第n行
                $sheetMajor -> setCellValue($letters[$key] . $row, $value['title']);//所有
                if (!empty($value['children'])) {
                    foreach ($value['children'] as $value2) {//市
                        $row2 = 1;//属性sheet的第n行
                        $sheetMajor -> setCellValue($letters[$key] . ++$row, $value2['title']);
                        $sheetProp -> setCellValue($letters[$sheetPropCol] . $row2, $value2['title']);
                        if (!empty($value2['children'])) {
                            foreach ($value2['children'] as $value3) {//区/镇
                                $sheetProp -> setCellValue($letters[$sheetPropCol] . ++$row2, $value3['title']);
                            }
                            $spreadsheet -> addNamedRange(new NamedRange($value2['title'], $sheetProp, $letters[$sheetPropCol] . '2:' . $letters[$sheetPropCol] . $row2));//设置市=>区/镇联动
                        }
                        $sheetPropCol++;//属性sheet列+1
                    }
                    $spreadsheet -> addNamedRange(new NamedRange($value['title'], $sheetMajor, $letters[$key] . '2:' . $letters[$key] . $row));
                }
            }
            $spreadsheet -> addNamedRange(new NamedRange('major', $sheetMajor, 'A1:' . $letters[count($major)] . '1'));

            $sheetMajor -> setSheetState(Worksheet::SHEETSTATE_HIDDEN);//隐藏省市sheet
            $sheetProp -> setSheetState(Worksheet::SHEETSTATE_HIDDEN);//隐藏市区sheet


            $maxRows = 200;//模板填充行数
            foreach (range(2, $maxRows) as $row) {
                //200行内列数E、F、G、H、I设置成可以下拉选择
                $this->setValidation($sheetMain, "E${row}", '"' . $biaoduanStr . '"');
                $this -> setValidation($sheetMain, "F${row}", '=major');
                $this -> setValidation($sheetMain, "G${row}", "=INDIRECT(F${row})");//indirect间接的，以某个坐标为依托，联级
                $this -> setValidation($sheetMain, "H${row}", "=INDIRECT(G${row})");//indirect间接的，以某个坐标为依托，联级
                $this -> setValidation($sheetMain, "I${row}", '"' . $typeStr . '"');

                $this -> setInputRule($sheetMain, "A${row}", DataValidation::TYPE_DECIMAL);//设置输入数字类型
                $this -> setInputRule($sheetMain, "D${row}", DataValidation::TYPE_DECIMAL);//设置输入数字类型
                $this -> setInputRule($sheetMain, "K${row}", DataValidation::TYPE_TIME);//设置输入时间类型
            }
            //调整sheet样式
            $styleArray = ['font' => ['bold' => true], 'alignment' => ['horizontal' => Alignment::HORIZONTAL_CENTER]];
            $sheetMain -> getStyle('A1:L1') -> applyFromArray($styleArray);

            $sheetMain -> getColumnDimension('B') -> setWidth(25);
            $sheetMain -> getColumnDimension('C') -> setWidth(25);
            $sheetMain -> getColumnDimension('E') -> setWidth(20);
            $sheetMain -> getColumnDimension('F') -> setWidth(20);
            $sheetMain -> getColumnDimension('G') -> setWidth(20);
            $sheetMain -> getColumnDimension('H') -> setWidth(20);
            $sheetMain -> getColumnDimension('K') -> setWidth(20);
            //$sheetMain->getColumnDimension('H')->setAutoSize(true); // 自动宽度，列表无用

            //定义文件名称，需要带有定义的后缀名
            $filename = 'test.xlsx';
            ob_end_clean(); //清除缓冲区,避免乱码
            //将输出重定向到客户端的web浏览器
            header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
            header('Content-Disposition: attachment;filename="' . $filename . '"');
            header('Cache-Control: max-age=0');
            //如果浏览器为IE9
            header('Cache-Control: max-age=1');
            //如果通过SSL向IE提供服务
            header('Expires: Mon, 26 Jul 1997 05:00:00 GMT');
            header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT');
            header('Cache-Control: cache, must-revalidate');//HTTP/1.1
            header('Pragma: public');//HTTP/1.0
            $writer = IOFactory ::createWriter($spreadsheet, 'Xlsx');
            $writer -> save('php://output');
            exit;
    }
    /**
     * 设置某个单元格的下拉列表规则
     * @param Worksheet $sheet
     * @param [string] $cellPoint 单元格坐标. A1
     * @param [sting] $format 公式
     * @return void
     */
    protected function setValidation(Worksheet $sheet, $cellPoint, $format)
    {
        $validation = $sheet -> getCell($cellPoint) -> getDataValidation();
        $validation -> setType(DataValidation::TYPE_LIST);
        $validation -> setErrorStyle(DataValidation::STYLE_INFORMATION);
        $validation -> setAllowBlank(false);
        $validation -> setShowInputMessage(true);
        $validation -> setShowErrorMessage(true);
        $validation -> setShowDropDown(true);

        $validation -> setErrorTitle('输出错误');
        $validation -> setError('值不在列表中');
        $validation -> setPromptTitle('请选择');
        $validation -> setPrompt('请从列表中选择一个值');
        $validation -> setFormula1($format);
        // $validation->setFormula1('=major');//使用某个公式
        // $validation->setFormula1('"A1,A2,A3"');//可以直接写列表，用逗号分隔,最多255字符
        // $objValidation->setFormula1('=INDIRECT(F5)');//indirect间接的，以某个坐标为依托，联级
    }

    /**
     * 设置某个单元格的输入规则
     * @param Worksheet $sheet
     * @param [string] $cellPoint 单元格坐标. A1
     * @param [sting] type 输入类型 DataValidation::TYPE_LIST
     * @return void
     */
    protected function setInputRule(Worksheet $sheet, $cellPoint, $type)
    {
        $validation = $sheet -> getCell($cellPoint) -> getDataValidation();
        $validation -> setType($type);
        $validation -> setErrorStyle(DataValidation::STYLE_INFORMATION); //输错了的话显示的提示属于哪个级别
        $validation -> setAllowBlank(false);
        $validation -> setShowInputMessage(true);
        $validation -> setShowErrorMessage(true);
        $validation -> setErrorTitle('输入格式有误');
    }
    public function exportExcelqqq()
    {
        // 创建新Excel文档
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();

// 设置下拉选项数据
        $options = ['PHP', 'Java', 'Python', 'C++'];
        $sheet->fromArray($options, null, 'Z1'); // 将选项放在Z列备用

// 设置数据验证（下拉列表）
        $validation = $sheet->getCell('A1')->getDataValidation();
        $validation->setType(\PhpOffice\PhpSpreadsheet\Cell\DataValidation::TYPE_LIST);
        $validation->setFormula1('$Z$1:$Z$4');
        $validation->setAllowBlank(false);
        $validation->setShowDropDown(true);

// 添加VBA宏实现多选功能
        $vbaCode = <<<VBA
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rngDV As Range
    Dim oldVal As String
    Dim newVal As String
    
    If Target.Count > 1 Then GoTo exitHandler
    On Error Resume Next
    Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation)
    On Error GoTo exitHandler
    
    If Not rngDV Is Nothing Then
        If Not Intersect(Target, rngDV) Is Nothing Then
            Application.EnableEvents = False
            newVal = Target.Value
            Application.Undo
            oldVal = Target.Value
            Target.Value = newVal
            
            If oldVal = "" Then
                ' 第一次选择
            ElseIf newVal = "" Then
                ' 清空选择
            Else
                ' 处理多选逻辑
                If InStr(1, oldVal, newVal) = 0 Then
                    Target.Value = oldVal & ", " & newVal
                Else
                    Target.Value = Replace(oldVal, newVal, "")
                    Target.Value = Replace(Target.Value, ", ,", ",")
                End If
            End If
        End If
    End If

exitHandler:
    Application.EnableEvents = True
End Sub
VBA;

// 保存为启用宏的Excel文件
        $writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
        $writer->setIncludeCharts(true);
       // $writer->getPhpSpreadsheet()->getActiveSheet()->getParent()->setMacrosCode($vbaCode);
        $writer->save('multiselect_dropdown.xlsm');

        echo "Excel文件已生成，请启用宏使用多选功能";
    }
    public function bao(){
        $t = time();
        $list = Db::name("work")->select();
        foreach ($list as $k=>$v){
            $product = Db::name("product")->where(['id'=>$v['product_id']])->find();
            $pro_time = strtotime($product['producetime'])+$product['warranty']*86400;
            $up = [];
            if($pro_time > $t){
                $up['pro_bao'] = 1;//在保
            }else{
                $up['pro_bao'] = 0;//过保
            }
            if($v['is_bao'] == 0){
                $work_time = strtotime($v['real_pay_time'])+365*86400;
                if($work_time > $t){
                    $up['work_bao'] = "1";//在保

                }else{
                    $up['work_bao'] = "0";//过保

                }
            }else{
                $up['work_bao'] = "1";//在保
            }
            Db::name("work")->where(['id'=>$v['id']])->update($up);
        }

    }
    /*public function ggpu(){
        $list = Db::name("product")->select();
        foreach ($list as $k=>$v){
            if($v['productname_id'] > 0 ){
                $data = Db::name("productname")->where(['id'=>$v['productname_id']])->find();
                if($v['name'] != $data['name']){
                    Db::name("product")->where(['id'=>$v['id']])->update(['name'=>$data['name']]);
                }
            }

        }
    }*/
}
